package com.huaxia.dao.newAdd;

import com.huaxia.pojo.newAdd.BranchNewAdd;

import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/05 17:25
 */

public interface BranchNewAddMapper {

    @Select("select t1.BRANCHSNAME," +
            "NVL(t2.branchNewAgent,0)branchNewAgent," +
            "NVL(t3.branchAllAgent,0)branchAllAgent," +
            "NVL(t4.branchChargeNum,0)branchChargeNum," +
            "NVL(t5.branchChargeIncrease,0)branchChargeIncrease," +
            "round(decode(NVL(t4.branchChargeNum,0),0,-9999,NVL(t5.branchChargeIncrease,0)*100/t4.branchChargeNum),1) branchChargeYield from\n" +
            "  SD_BRANCH t1 left join  \n" +
            "  (select t.BRANCHCODE,NVL(count(t.BRANCHCODE),0)branchNewAgent from D_AGENT_PROVINCE_YX2 t where TO_CHAR(t.EMPLOYDATE,'YYYY/MM')=TO_CHAR(sysdate,'YYYY/MM') and AGENTSTATE='在职' and t.name <> '客户服务专员' group by t.BRANCHCODE\n" +
            "  )t2 on t1.branchcode=t2.branchcode left join\n" +
            "  (select t.BRANCHCODE,count(t.BRANCHCODE) branchAllAgent from D_AGENT_PROVINCE_YX2 t where AGENTSTATE='在职' and t.name <> '客户服务专员' group by t.BRANCHCODE\n" +
            "  )t3 on t1.branchcode=t3.branchcode left join\n" +
            "  (select t.BRANCHCODE,count(t.BRANCHCODE) branchChargeNum from D_AGENT_PROVINCE_YX2 t where\n" +
            "   t.AGENTSTATE='在职' and t.name <> '客户服务专员' and t.AGENTGRADE in('BC','SBC','BM','SBM','AS','SAS') group by t.BRANCHCODE\n" +
            "  )t4 on t1.branchcode=t4.branchcode left join\n" +
            "  (select b.BRANCHCODE,nvl(count(b.BRANCHCODE),0) branchChargeIncrease from\n" +
            "     (select t.RECOMMAGENTCODE from D_AGENT_PROVINCE_YX2 t where \n" +
            "     TO_CHAR(t.EMPLOYDATE,'YYYY/MM')=TO_CHAR(sysdate,'YYYY/MM') and  t.AGENTSTATE='在职' GROUP BY t.RECOMMAGENTCODE)a,\n" +
            "     (select t.BRANCHCODE,t.AGENTCODE  from D_AGENT_PROVINCE_YX2 t where\n" +
            "     t.AGENTSTATE='在职' and t.name <> '客户服务专员' and t.AGENTGRADE in('BC','SBC','BM','SBM','AS','SAS') \n" +
            "     )b where b.AGENTCODE=a.RECOMMAGENTCODE group by b.BRANCHCODE\n" +
            "  )t5 on t1.branchcode=t5.branchcode order by t2.branchNewAgent DESC nulls last")
    List<BranchNewAdd> getBranchNewAdd();
    
    

}

